#!/usr/bin/env python
# -*- coding: utf-8 -*-

#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.handle_mongo import *
from lib.switch_code import *
from zhineng_conn import *
from config_tools import *
from imc_conn import *
from lib.load_data import *
import urllib
import json
import time
import math
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

def get_esc_info(date,s58):
    bid_conn = set_bid_conn()
    dict_conn = set_dict_conn()
    ib_conn = set_ib_conn()
    line_list = []
    stat_table='esc_info_data'
    
    cate_id = 29
    defualt_full_path = '4,29'
    default_list_name = 'ershouche'
    leixing = '2539'
    pinbai = '5866'
    chexi = '5867'
    chexing = '5868'    
    jiage = '1194'
    seprice = '5864'
    cheling = '5883'
    pailiang = '2557'
    licheng = '1195'
    
    new_param_id = "5461"
    
    for i in range(8):
        query = "select distinct entity_id from creative_info_%s where entity_type=0 and date(add_time)='%s'"%(i,date)
        items = bid_conn.get_results(query)
        if len(items) == 0:
            continue
        
        for item in items:
            info_id = item[0]
            #print info_id
            list_name = ''
            
            params = ''
            info = None
            try:
                info = s58.get_info(info_id)
            except Exception:
                continue
            
            
            #print info
            if info == None:
                continue
            info_cate_id = info.getCateID()
            url = info.getUrl()
            if info_cate_id != cate_id:
                continue
            state = info.getState()
            params = info.getParams()
            #print params
            if params == None or params == '':
                continue
            p_list = None
            try:
                params = params.replace("'",'"')
                #print params
                p_list = eval(params)
            except Exception:
                #print params
                continue
           
            #jia ge        
            price = get_param_val(p_list,jiage)
            if price == None:
                price = '0'
            se_price = get_param_val(p_list,seprice)
            if se_price == None:
                se_price = ''
            #pai liang        
            pai_liang = get_param_val(p_list,pailiang)
            if pai_liang == None:
                pai_liang = ''
            #print "pai_liang:",pai_liang
            #li cheng        
            li_cheng = get_param_val(p_list,licheng)
            if li_cheng == None:
                li_cheng = '0'
            #print "li_cheng:",li_cheng
            #che ling
            car_age = '0'
            cheling_val = get_param_val(p_list,cheling)
            if cheling_val == None:
                cheling_val = '0'        
            q = "select ifnull(title,'') from t_cmcs_param where cate_id=%s and param_id=%s and param_name='%s' limit 1"%(cate_id,cheling,cheling_val)
            #print q
            age = dict_conn.get_results(q)
            if len(age):
                car_age = age[0][0]
            
            #che xing
            che_xing_cal = ''
            full_path = ''

            new_param = get_param_val(p_list,new_param_id)
            #print new_param
            if new_param == None:            
                chexing_val = get_param_val(p_list,chexing)
                if chexing_val == None:
                    chexing_val = ''
                q = "select ifnull(title,'') from t_cmcs_param where  param_name='%s' limit 1"%chexing_val
                #print q
                c_x = dict_conn.get_results(q)
                if len(c_x):
                    che_xing_cal = c_x[0][0]
                    
                #che xi
                chexi_val = get_param_val(p_list,chexi)
                if chexi_val != None and chexi_val != '0' :
                    q = "select list_name from t_cmcs_param where param_name='%s' limit 1"%chexi_val
                    che_list = dict_conn.get_results(q)
                    if len(che_list)>0:
                        list_name = che_list[0][0]
                else:
                    pinbai_val = get_param_val(p_list,pinbai)
                    if pinbai_val != None and pinbai_val != '0' :
                        q = "select list_name from t_cmcs_param where param_name='%s' limit 1"%pinbai_val
                        che_list = dict_conn.get_results(q)
                        if len(che_list)>0:
                            list_name = che_list[0][0]
                    
                    if list_name  == None and list_name =='':
                        leixing_val = get_param_val(p_list,leixing)
                        if leixing_val != None and leixing_val != '0' :
                            q = "select list_name from t_cmcs_param where cate_id=%s and param_id=%s and param_name='%s' limit 1"%(cate_id,leixing,leixing_val)
                            che_list = dict_conn.get_results(q)
                            if len(che_list)>0:
                                list_name = che_list[0][0]
                #list name               
                if list_name  == None and list_name =='':
                    list_name = default_list_name
                
                #cate
                q = "select ifnull(fullpath,'') from t_dispcategory where listname='%s' "%list_name
                cate_path = dict_conn.get_results(q)
                if len(cate_path)>0:
                    full_path = cate_path[0][0]
                if full_path == '':
                    full_path = defualt_full_path
            else:
                p_dict = urldecode(new_param.replace('amp;',''))
                #print p_dict
                chexi_name = ''
                pinpai_name = ''
                leixing_name =''
                leixing_cate_id = 0
                pinpai_id = 0
                
                if p_dict.has_key('carchexing'):
                    che_xing_cal = p_dict['carchexing'][0]
                    
                if p_dict.has_key('objecttype'):
                    leixing_name = p_dict['objecttype'][0]
                if p_dict.has_key('brand'):
                    pinpai_name = p_dict['brand'][0]                  
                if p_dict.has_key('chexi'):
                    chexi_name = p_dict['chexi'][0]
                
                q = "select ifnull(fullpath,''),dispcategoryid from t_dispcategory where catename='%s' "%leixing_name
                #print q
                cate = dict_conn.get_results(q)
                if len(cate)>0:
                    full_path = cate[0][0]
                    leixing_cate_id = cate[0][1]
                
                if pinpai_name != '':
                    q = "select ifnull(fullpath,''),dispcategoryid from t_dispcategory where pid=%s and catename='%s' "%(leixing_cate_id,pinpai_name)
                    cate = dict_conn.get_results(q)
                    if len(cate)>0:
                        full_path = cate[0][0]
                        pinpai_id = cate[0][1]
                        
                if chexi_name != '':
                    q = "select ifnull(fullpath,''),dispcategoryid from t_dispcategory where pid=%s and catename='%s' "%(pinpai_id,chexi_name)
                    cate = dict_conn.get_results(q)
                    if len(cate)>0:
                        full_path = cate[0][0]
                        pinpai_id = cate[0][1]
                        
            #print new_param,full_path
            if full_path == '':
                full_path = defualt_full_path               
                
            q = "select dispcategoryid,catename from t_dispcategory where dispcategoryid in (%s) order by depth asc"%full_path
            cates = dict_conn.get_results(q)
            
            cat1 = 0
            cat1name = ''
            cat2 = 0
            cat2name=''
            cat3 = 0
            cat3name=''
            cat4 = 0
            cat4name=''
            cat5 = 0
            cat5name=''
            if cates != None:
                if len(cates) > 0:
                    cat1 = cates[0][0]
                    cat1name = cates[0][1]
                if len(cates) > 1:
                    cat2 = cates[1][0]
                    cat2name=cates[1][1]
                if len(cates) > 2:
                    cat3 = cates[2][0]
                    cat3name=cates[2][1]
                if len(cates) > 3:
                    cat4 = cates[3][0]
                    cat4name=cates[3][1]
                if len(cates) > 4:
                    cat5 = cates[4][0]
                    cat5name=cates[4][1]
            url = "%s%sx.shtml"%(url,info_id)
            param = [info_id,cat1,cat1name,cat2,cat2name,cat3,cat3name,cat4,cat4name,cat5,cat5name,che_xing_cal,url,price,se_price,car_age,cheling_val,pai_liang,li_cheng,params,cate_id,state]
            line_list.append('\t'.join(map(lambda i : str(i), param)))
    if len(line_list) > 0:   
        cont = '\n'.join(line_list)
        cont += '\n'
        path = "/work3/opt/analytic/lh_tmp_data/esc_info/"
        file_name = "che_info_%s.txt"%date.replace('-',"")
        writeTextToFile(path,file_name,"w",cont)    
        #print cont
        load_data(ib_conn,path+file_name,stat_table,'\t')      
    
    bid_conn.close()
    dict_conn.close()
    ib_conn.close()


def get_campaign(date):
    bid_conn = set_bid_conn()
    stat_conn = set_stats_conn()    
    line_list = []
    stat_table='campaign'
    
    query = "select * from campaign where date(add_time)='%s'"%(date)
    items = bid_conn.get_results(query)
    for item in items:
        campaign_id = item[0]
        campaign_name = item[1]
        user_id = item[2]
        hasenddate = item[3]
        start_time = item[4]
        end_time = item[5]
        state = item[6]
        budget = item[7]
        budgetover = item[8]
        schedule = item[9]
        ad_source = item[10]
        add_time = item[11]
        add_user_id = item[12]
        mod_time = item[13]
        mod_user_id = item[14]
        
        param = [campaign_id,campaign_name,user_id,hasenddate,start_time,end_time,state,budget,budgetover,schedule,ad_source,add_time,add_user_id,mod_time,mod_user_id]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/campaign/"
    file_name = "campaign_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')    
    remove_file(path+file_name)
    
    bid_conn.close()
    stat_conn.close()
    
def get_creative_info(date,s58):
    bid_conn = set_bid_conn()
    stat_conn = set_stats_conn()
    dict_conn = set_dict_conn()
    shop_conn = set_shop_conn()
    wb_conn = set_58_conn()
    line_list = []
    stat_table='creative_info'
    
    for i in range(8):
        query = "select creative_id,subscribe_id,channel_id,campaign_id,user_id,title,`describe`,target_url,pic_url,state,entity_id,entity_type,entity_state,refuse_reason,mod_info,ad_source,auction_type,source_id,add_time,add_user_id,mod_time,mod_user_id,entity_info from creative_info_%s where date(add_time)='%s'"%(i,date)
        #print query
        items = bid_conn.get_results(query)
        if len(items) == 0:
            continue
        for item in items:
            creative_id = item[0]
            subscribe_id  = item[1]
            channel_id  = item[2]
            campaign_id  = item[3]
            user_id  = item[4]
            title  = item[5]
            describe  = item[6]
            describe = describe.replace('\t','')
            target_url  = item[7]
            pic_url  = item[8]
            state  = item[9]
            entity_id  = item[10]
            entity_type  = item[11]
            entity_state  = item[12]
            refuse_reason  = item[13]
            mod_info  = item[14]
            
            ad_source  = item[15]
            auction_type  = item[16]
            source_id  = item[17]
            add_time  = item[18]
            add_user_id  = item[19]
            mod_time  = item[20]
            mod_user_id  = item[21]
            entity_info  = item[22]
            
            city = 0
            cate = 0
            if entity_type == 0 :
                info = None
                try:
                    info = s58.get_info(entity_id)
                except Exception:
                    pass
                if info != None:
                    city_tmp= info.getLocals()
                    cate = info.getCateID()
                    city = json.loads(city_tmp)[0]['L']
            elif entity_type == 1:
                q = "select city_id,area_id,local_id,cate_id from shop_info where shop_id=%s"%entity_id
                its = shop_conn.get_results(q)
                if len(its) > 0 :
                    tc = its[0][0]
                    ta = its[0][1]
                    tl = its[0][2]
                    cate = its[0][3]
                    if tl == 0:
                        if ta == 0:
                            city = tc
                        else:
                            city = ta
                    else:
                        city = tl
            elif entity_type == 2:
                q = "select city_id,area_id,local_id,cate1_id,cate2_id,cate3_id from product_info where shop_id=%s"%entity_id
                its = shop_conn.get_results(q)
                if len(its) > 0 :
                    tc = its[0][0]
                    ta = its[0][1]
                    tl = its[0][2]
                    cate1 = its[0][3]
                    cate2 = its[0][4]
                    cate3 = its[0][5]
                    
                    city_tmp = 0
                    if tl == 0:
                        if ta == 0:
                            city_tmp = tc
                        else:
                            city_tmp = ta
                    else:
                        city_tmp = tl
                    q = "select localid from t_displocal where displocalid=%s"%city_tmp
                    its = dict_conn.get_results(q)
                    if len(its) > 0 :
                        city = its[0][0]
                        
                    cate_tmp = 0
                    if cate3 == 0:
                        if cate2 == 0:
                            cate_tmp = cate1
                        else:
                            cate_tmp = cate2
                    else:
                        cate_tmp = cate3
                    q = "select cateid from t_dispcategory where dispcategoryid=%s"%cate_tmp
                    its = dict_conn.get_results(q)
                    if len(its) > 0 :
                        cate = its[0][0]
            elif entity_type == 3:
                q = "select city_id,area_name,cate_id1,cate_name2 from portal_info where portal_id=%s"%entity_id
                #print q
                its = wb_conn.get_results(q)
                if len(its) > 0 :
                    tc = its[0][0]
                    area = its[0][1]
                    tc = its[0][2]
                    cate2_name = its[0][3].split("|")[0]
                    
                    if area == '':
                        city = tc
                    else:
                        q = "select localid from t_local where localname='%s'"%area
                        its = dict_conn.get_results(q)
                        if len(its) > 0 :
                            city = its[0][0]
                    
                    if cate2_name == '':
                        q = "select cateid from t_dispcategory where dispcategoryid=%s"%tc
                        its = dict_conn.get_results(q)
                        if len(its) > 0 :
                            cate = its[0][0]
                    else:
                        q = "select cateid from t_dispcategory where catename='%s'"%cate2_name
                        its = dict_conn.get_results(q)
                        if len(its) > 0 :
                            cate = its[0][0]
                    
                    
            #print city
            
            if cate == None:
                cate = 0
            dc = get_gs_cates(cate,dict_conn)    
            cat1 = 0
            cat1name = ''
            cat2 = 0
            cat2name=''
            cat3 = 0
            cat3name=''
            if dc != None:
                if len(dc) > 0:
                    cat1 = dc[0][0]
                    cat1name = dc[0][1]
                if len(dc) > 1:
                    cat2 = dc[1][0]
                    cat2name=dc[1][1]
                if len(dc) > 2:
                    cat3 = dc[2][0]
                    cat3name=dc[2][1]
            
            city_id = 0
            city_name = ''
            area_id = 0
            area_name = ''
            local_id= 0
            local_name = ''
            if city == None:
                city = 0
            dc = get_gs_citys(city,dict_conn)
            if dc != None:
                if len(dc) >0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
        
            param = [creative_id,subscribe_id,channel_id,campaign_id,user_id,title,describe,target_url,pic_url,state,entity_id,entity_type,entity_state,refuse_reason,mod_info,source_id,ad_source,auction_type,add_time,add_user_id,mod_time,mod_user_id,entity_info,city_id,city_name,area_id,area_name,local_id,local_name,cat1,cat1name,cat2,cat2name,cat3,cat3name]
            line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    cont = cont +'\n'
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/creative_info/"
    file_name = "creative_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')    
    #remove_file(path+file_name)
    
    shop_conn.close()
    wb_conn.close()
    dict_conn.close()
    bid_conn.close()
    stat_conn.close()

def get_subscribe(date):
    bid_conn = set_bid_conn()
    stat_conn = set_stats_conn()    
    line_list = []
    stat_table='subscribe'
    
    query = "select * from subscribe where date(add_time)='%s'"%(date)
    items = bid_conn.get_results(query)
    for item in items:
        subscribe_id = item[0]
        channel_id = item[1]
        channel_info = item[2]
        iswholeput = item[3]
        campaign_id = item[4]
        user_id = item[5]
        bid = item[6]
        budget = item[7]
        budgetover = item[8]
        schedule = item[9]
        auction_type = item[10]
        state = item[11]
        ad_source = item[12]
        add_time = item[13]
        add_user_id = item[14]
        mod_time = item[15]
        mod_user_id = item[16]
        
        if campaign_id > 0:
            q = "select budget,budgetover from campaign where campaign_id=%s"%campaign_id
            its = bid_conn.get_results(q)
            if len(its) > 0 :
                budget = its[0][0]
                budgetover = its[0][1]
        
        param = [subscribe_id,channel_id,channel_info,iswholeput,campaign_id,user_id,bid,budget,budgetover,schedule,auction_type,state,ad_source,add_time,add_user_id,mod_time,mod_user_id]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/subscribe/"
    file_name = "subscribe_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')    
    remove_file(path+file_name)
    
    bid_conn.close()
    stat_conn.close()
        

def get_param_val(p_list,p_id):
    for p in p_list:
        if str(p.get("I")) == p_id:
            return p.get("V")
        
    return None
    
def urldecode(query):
    d = {}
    a = query.split('&')
    for s in a:
        if s.find('='):
            k,v = map(urllib.unquote, s.split('='))
            try:
                d[k].append(v)
            except KeyError:
                d[k] = [v]

    return d



def execute(date,s58):
    #get_subscribe(date)
    try:
        pass
        get_esc_info(date,s58)
    except Exception,e:
        print 'get_esc_info:',e
    
    try:
        pass
        get_campaign(date)
    except Exception,e:
        print 'get_campaign:',e
        
    try:
        pass
        get_creative_info(date,s58)
    except Exception,e:
        print 'get_creative_info:',e
        
    try:
        pass
        get_subscribe(date)
    except Exception,e:
        print 'get_subscribe:',e

def main():

   #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   s58=Service58()
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        #get_creative_info(stat_date,s58)
        execute( stat_date,s58) 
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)


if __name__=='__main__':
    main()
